﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Xml;
using System.ServiceModel;
using System.ServiceModel.Channels;
using System.Text.RegularExpressions;

namespace Twi.DataAccess.DBUtility
{
    public static class SqlExtension
    {
        public static DateTime GetDBCurrentDate()
        {
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                return cmd.GetDBCurrentDate();
            }
        }

        public static DateTime GetDBCurrentDate(this SqlCommand me)
        {
            me.CommandText = "SELECT GETDATE()";
            return (DateTime)me.ExecuteScalar();
        }

        public static void Initialize(this SqlCommand me, bool executeWithinTransaction)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
            conn.Open();
            me.Connection = conn;
            if (executeWithinTransaction)
            {
                SqlTransaction txn = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
                me.Transaction = txn;
            }
        }

        public static void SetTransactionWithConnection(this SqlCommand me, SqlTransaction txn)
        {
            me.Transaction = txn;
            me.Connection = txn.Connection;
            me.CommandTimeout = 120;
        }

        #region SetParameter

        public static SqlParameter SetParameter_InputOutput(this SqlCommand me, string paraName, SqlDbType type, int size = 0)
        {
            if (!paraName.StartsWith("@"))
            {
                paraName = "@" + paraName;
            }
            if (me.Parameters.Contains(paraName))
            {
                return me.Parameters[paraName];
            }
            else
            {
                SqlParameter para = new SqlParameter();
                para.ParameterName = paraName;
                para.Direction = ParameterDirection.InputOutput;
                para.SqlValue = DBNull.Value;
                para.SqlDbType = type;
                if (size != 0)
                {
                    para.Size = size;
                }
                me.Parameters.Add(para);
                return para;
            }
        }

        public static void SetParameter(this SqlCommand me, string paraName, object paraValue)
        {
            if (!paraName.StartsWith("@"))
            {
                paraName = "@" + paraName;
            }

            if (paraValue is XElement)
            {
                paraValue = new SqlXml(new XmlTextReader(paraValue.ToString(), XmlNodeType.Document, null));
            }
            else if (paraValue is IEnumerable<XElement>)
            {
                paraValue = (paraValue as IEnumerable<XElement>).Select(x => x.ToString()).Aggregate((x, y) => x + y);
            }

            if (me.Parameters.Contains(paraName))
            {
                me.Parameters[paraName].Value = paraValue;
            }
            else
            {
                var para = new SqlParameter(paraName, paraValue);
                if (paraValue is XElement)
                {
                    para.SqlDbType = SqlDbType.Xml;
                }
                me.Parameters.Add(para);
            }
        }

        public static void SetParameter(this SqlCommand me, SqlParameter para)
        {
            if (para == null) { return; }
            if (me.Parameters.Contains(para.ParameterName))
            {
                me.Parameters[para.ParameterName].Value = para.Value;
            }
            else
            {
                me.Parameters.Add(para);
            }
        }

        public static void SetParameters(this SqlCommand me, SqlParameter[] parameters)
        {
            foreach (var x in parameters)
            {
                me.SetParameter(((ICloneable)x).Clone() as SqlParameter);
            }
        }

        #endregion

        #region get

        public static string GetConcatenate(this SqlCommand me, string separartor, string format = "{0}")
        {
            string concat = "";
            using (SqlDataReader rdr = me.ExecuteReader())
            {
                while (rdr.Read())
                {
                    if (concat != "")
                    {
                        concat += separartor;
                    }
                    concat += string.Format(format, rdr.GetValue(0));
                }
            }
            return concat;
        }

        public static XElement GetSingle(this SqlCommand me, string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (SqlDataReader rdr = me.ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (tableName != "Item" && (GetMetaDataTable(tableName).Rows.OfType<DataRow>().SingleOrDefault(x => x["字段名"].ToString() == rdr.GetName(i) && x["类型"].ToString() == "xml") != null))
                        {
                            xe.Add(rdr.GetXElement(i, true));
                        }
                        else
                        {
                            xe.Add(rdr.GetXElement(i));
                        }
                    }
                }
                else
                {
                    throw new Exception("无法检索到唯一记录");
                }
            }
            return xe;
        }

        public static XElement GetSingleOrDefault(this SqlCommand me, string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (SqlDataReader rdr = me.ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (tableName != "Item")
                        {
                            DataRow dr = GetMetaDataTable(tableName).Rows.OfType<DataRow>().SingleOrDefault(x => x["字段名"].ToString() == rdr.GetName(i));
                            if (dr != null && dr["类型"].ToString() == "xml")
                            {
                                xe.Add(rdr.GetXElement(i, true));
                            }
                            else
                            {
                                xe.Add(rdr.GetXElement(i));
                            }
                        }
                        else
                        {
                            xe.Add(rdr.GetXElement(i));
                        }
                    }
                }
                else
                {
                    return null;
                }
            }
            return xe;
        }

        public static XElement GetSingleOrEmpty(this SqlCommand me, string tableName = "Item")
        {
            XElement xe = new XElement(tableName);
            using (SqlDataReader rdr = me.ExecuteReader())
            {
                if (rdr.Read())
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        if (tableName != "Item" && GetMetaDataTable(tableName).Rows.OfType<DataRow>().Single(x => x["字段名"].ToString() == rdr.GetName(i))["类型"].ToString() == "xml")
                        {
                            xe.Add(rdr.GetXElement(i, true));
                        }
                        else
                        {
                            xe.Add(rdr.GetXElement(i));
                        }
                    }
                }
                else
                {
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xe.Add(new XElement(rdr.GetName(i)));
                    }
                }
            }
            return xe;
        }

        public static XElement GetMultiple(this SqlCommand me, string tableName = "Item")
        {
            XElement xeMultiple = new XElement(string.Format("{0}List", tableName));

            #region 旧代码(用SqlDataReader查数据)
            //using (SqlDataReader rdr = me.ExecuteReader())
            //{
            //    while (rdr.Read())
            //    {
            //        XElement xeItem = new XElement(tableName);
            //        for (int i = 0; i < rdr.FieldCount; i++)
            //        {
            //            XElement xeCell = rdr.GetXElement(i);
            //            if (xeCell.Name.LocalName.StartsWith("override_")) //是否是从其他表中关联过来的覆盖值, 通常用于主表用*号概括所有字段, 从表显示部分需要覆盖的字段 
            //            {
            //                xeItem.Element(xeCell.Name.LocalName.Replace("override_", "")).SetValue(xeCell.Value);
            //            }
            //            else
            //            {
            //                xeItem.Add(xeCell);
            //            }
            //        }
            //        xeMultiple.Add(xeItem);
            //    }
            //}
            #endregion

            #region 新加替代代码(用SqlDataAdapter查数据) 2012-08-02添加
            using (DataTable dt = new DataTable(tableName))
            {
                SqlDataAdapter dataAdapter = new SqlDataAdapter(me);
                dataAdapter.Fill(dt);
                foreach (DataRow dr in dt.Rows)
                {
                    XElement xeItem = new XElement(tableName);
                    foreach (DataColumn column in dt.Columns)
                    {
                        XElement xeCell = column.GetXElement(dr[column.ColumnName]);
                        if (xeCell.Name.LocalName.StartsWith("override_")) //是否是从其他表中关联过来的覆盖值, 通常用于主表用*号概括所有字段, 从表显示部分需要覆盖的字段 
                        {
                            xeItem.Element(xeCell.Name.LocalName.Replace("override_", "")).SetValue(xeCell.Value);
                        }
                        else
                        {
                            xeItem.Add(xeCell);
                        }
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            #endregion

            return xeMultiple;
        }

        public static XElement GetXmlMultiple(this SqlCommand me)
        {

            using (XmlReader xr = me.ExecuteXmlReader())
            {
                if (xr.IsStartElement())
                {
                    XElement xeRtv = XElement.Load(xr);
                    return xeRtv;
                }
                else
                {
                    return null;
                }
            }
        }

        /// <summary>
        /// 通用分页获取数据方法
        /// </summary>
        /// <param name="elementName"></param>
        /// <param name="whereClause"></param>
        /// <param name="selectClause"></param>
        /// <param name="orderByClause"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <returns></returns>
        public static XElement GetData(string whereClause, string selectClause, string orderByClause, int pageSize, int pageIndex)
        {
            XElement xe = null;
            string _sql1 = "SELECT COUNT(1) FROM " + whereClause;
            int itemAmount = (int)SqlHelper.ExecuteScalar(CommandType.Text, _sql1);
            int pageAmount = 0;
            if (itemAmount > 0)
            {
                pageAmount = itemAmount / pageSize + (itemAmount % pageSize == 0 ? 0 : 1);
                //处理列表信息
                string _sql2 = string.Format(@"WITH VIEW_TEMP AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS [RowNumber], {0} FROM {1})
select {4} as ItemAmount,{5} as PageAmount,(SELECT * FROM VIEW_TEMP  WHERE [RowNumber] BETWEEN {2} AND {3} ORDER BY [RowNumber] for xml path('Item'),type,elements XSINIL,root('ItemList')) for xml path('DataRoot'),elements
", selectClause, whereClause, pageSize * pageIndex + 1, pageSize * (pageIndex + 1), itemAmount, pageAmount);

                xe = SqlHelper.ExecuteXElement(_sql2);
                return xe;

            }
            else
            {
                xe = new XElement("DataRoot");
                xe.Add(new XElement("ItemAmount", itemAmount));
                xe.Add(new XElement("PageAmount", pageAmount));
                return xe;
            }
        }

        #region 获得数据
        /// <summary>
        /// 获得数据
        /// </summary>
        /// <param name="me"></param>
        /// <param name="masterTableName"></param>
        /// <param name="tablesSource"></param>
        /// <param name="searchCondition"></param>
        /// <param name="selectColumns"></param>
        /// <param name="orderExpression"></param>
        /// <param name="xePage"></param>
        /// <param name="subSearchCondition"></param>
        /// <param name="subOrderExpression"></param>
        /// <returns></returns>
        public static XElement[] GetMultiple(this SqlCommand me, string masterTableName
          , string tablesSource, string searchCondition, string selectColumns, string orderExpression, XElement xePage, string subSearchCondition = "", string subOrderExpression = "")
        {
            if (xePage.Element("PageIndex") == null) //不需要分页
            {
                me.CommandText = "SELECT " + selectColumns + " FROM " + tablesSource + " " + searchCondition + " ORDER BY " + orderExpression;
                return new XElement[] { me.GetMultiple(masterTableName) };
            }
            else if (xePage.Element("PageIndex").Value == "-1") //分页转不分页，导出全部记录时使用
            {
                me.CommandText = string.Format(@"WITH VIEW_SOURCE AS (SELECT {0} FROM {1} WHERE 1 = 1 {2} {3})"
               , subOrderExpression == "" ? selectColumns : ("TOP 100 PERCENT " + selectColumns), tablesSource, subSearchCondition
               , subOrderExpression == "" ? "" : (" ORDER BY " + subOrderExpression));
                //处理自定义的排序子句
                string orderby = "";
                if (!string.IsNullOrEmpty(orderExpression))
                {
                    //同时兼容 a.ApplicationFormNo DESC, SampleNo ASC
                    orderby = orderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                }

                string subOrderby = "";
                if (!string.IsNullOrEmpty(subOrderExpression))
                {
                    subOrderby = subOrderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                }

                me.CommandText = me.CommandText + Environment.NewLine + "SELECT VIEW_SOURCE.* FROM VIEW_SOURCE " + searchCondition;
                string sqlOrderby = string.IsNullOrEmpty(orderby) ? (string.IsNullOrEmpty(subOrderExpression) ? "" : subOrderby) : orderby;
                if (string.IsNullOrEmpty(sqlOrderby))
                {
                    me.CommandText += " " + sqlOrderby;
                }
                return new XElement[] { me.GetMultiple(masterTableName) };
            }
            else
            {
                return GetMultiple(me, masterTableName, tablesSource, searchCondition, selectColumns, orderExpression, int.Parse(xePage.Element("PageSize").Value), int.Parse(xePage.Element("PageIndex").Value), subSearchCondition, subOrderExpression);
            }


        }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <param name="me"></param>
        /// <param name="masterTableName">主表名称</param>
        /// <param name="tablesSource">数据的来源from后（主表 inner join 从表 on 。。）</param>
        /// <param name="searchCondition">搜索条件 where 后（..）/param>
        /// <param name="selectColumns">select后</param>
        /// <param name="orderExpression"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="subSearchCondition">跟在SQl内部的Where条件</param>
        /// <returns></returns>
        public static XElement[] GetMultiple(this SqlCommand me, string masterTableName
            , string tablesSource, string searchCondition, string selectColumns, string orderExpression, int pageSize, int pageIndex, string subSearchCondition, string subOrderExpression)
        {
            string source = string.Format(@"WITH VIEW_SOURCE AS (SELECT {0} FROM {1} WHERE 1 = 1 {2} {3})"
                , subOrderExpression == "" ? selectColumns : ("TOP 100 PERCENT " + selectColumns), tablesSource, subSearchCondition
                , subOrderExpression == "" ? "" : (" ORDER BY " + subOrderExpression));

            //处理分页信息
            me.CommandText = source + Environment.NewLine + string.Format("SELECT COUNT(1) FROM VIEW_SOURCE {0} ", searchCondition);
            int itemAmount = (int)me.ExecuteScalar();
            int pageAmount = itemAmount / pageSize + (itemAmount % pageSize == 0 ? 0 : 1);
            XElement xeItemAmount = new XElement("ItemAmount", itemAmount);
            XElement xePageAmount = new XElement("PageAmount", pageAmount);

            XElement xeMultiple = new XElement(string.Format("{0}List", masterTableName ?? "Item"));

            if (itemAmount == 0)
            {
                return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
            }

            //处理自定义的排序子句
            string orderby = "";
            if (!string.IsNullOrEmpty(orderExpression))
            {
                //同时兼容 a.ApplicationFormNo DESC, SampleNo ASC
                orderby = orderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            }

            string subOrderby = "";
            if (!string.IsNullOrEmpty(subOrderExpression))
            {
                subOrderby = subOrderExpression.Split(',').Select(x => x.Contains('.') ? x.Split('.')[1] : x).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            }

            string source_numbered = source + Environment.NewLine +
                string.Format(@", VIEW_SOURCE_ROWNUMBERED AS (SELECT TOP {0} ROW_NUMBER() OVER (ORDER BY {1}) AS [RowNumber], VIEW_SOURCE.* FROM VIEW_SOURCE {2})"
                , pageSize * (pageIndex + 1)
                , string.IsNullOrEmpty(orderby) ?
                (string.IsNullOrEmpty(subOrderExpression) ? "(SELECT NULL)" : subOrderby)
                : orderby
                , searchCondition);

            //处理列表信息
            me.CommandText = source_numbered + Environment.NewLine + string.Format(@"SELECT * FROM VIEW_SOURCE_ROWNUMBERED WHERE [RowNumber] > {0} ORDER BY [RowNumber]"
                , pageSize * pageIndex);


            using (SqlDataReader rdr = me.ExecuteReader())
            {
                while (rdr.Read())
                {
                    XElement xeItem = new XElement(masterTableName ?? "Item");
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {
                        xeItem.Add(rdr.GetXElement(i));
                    }
                    xeMultiple.Add(xeItem);
                }
            }
            return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
        }
        #endregion

        #region 获得信息(2012-07-31新扩展方法,分页或不分页)
        /// <summary>
        /// 获得信息(2012-07-31新扩展方法,分页或不分页)
        /// </summary>
        /// <param name="me"></param>
        /// <param name="sqlSelect">查询对象</param>
        /// <param name="xePage">分页信息</param>
        /// <returns></returns>
        public static XElement[] GetMultiple(this SqlCommand me, SqlSelect sqlSelect, XElement xePage = null)
        {
            string masterTableName = sqlSelect.PrimaryTableName;
            if (xePage == null || xePage.Element("PageIndex") == null) //不需要分页
            {
                me.CommandText = sqlSelect.GetSqlStatement();
                return new XElement[] { me.GetMultiple(masterTableName) };
            }
            else
            {
                sqlSelect.SetPageInfo(xePage);

                //处理分页信息
                me.CommandText = sqlSelect.GetCountSqlStatement();
                int itemAmount = (int)me.ExecuteScalar();
                int pageAmount = itemAmount / sqlSelect.PageSize + (itemAmount % sqlSelect.PageSize == 0 ? 0 : 1);
                XElement xeItemAmount = new XElement("ItemAmount", itemAmount);
                XElement xePageAmount = new XElement("PageAmount", pageAmount);

                XElement xeMultiple = new XElement(string.Format("{0}List", masterTableName));

                if (itemAmount == 0)
                {
                    return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
                }

                me.CommandText = sqlSelect.GetPageSqlStatement();
                using (SqlDataReader rdr = me.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        XElement xeItem = new XElement(masterTableName);
                        for (int i = 0; i < rdr.FieldCount; i++)
                        {
                            xeItem.Add(rdr.GetXElement(i));
                        }
                        xeMultiple.Add(xeItem);
                    }
                }
                return new XElement[] { xeItemAmount, xePageAmount, xeMultiple };
            }
        }
        #endregion



        private static XElement GetXElement(this SqlDataReader rdr, int i, bool isXml = false)
        {
            XElement xe = new XElement(rdr.GetName(i));
            if (rdr.GetValue(i) == DBNull.Value)
            {
                return xe;
            }
            if (isXml)
            {
                xe.Add(XElement.Parse(string.Format("<root>{0}</root>", rdr.GetValue(i))).Elements().ToArray());
            }
            else
            {
                xe.SetValue(rdr.GetValue(i).ToString().ReplaceLowOrderASCIICharacters());
            }
            return xe;
        }


        private static XElement GetXElement(this DataColumn dataColumn, object value, bool isXml = false)
        {
            XElement xe = new XElement(dataColumn.ColumnName);
            if (value == DBNull.Value)
            {
                return xe;
            }
            if (isXml)
            {
                xe.Add(XElement.Parse(string.Format("<root>{0}</root>", value)).Elements().ToArray());
            }
            else
            {
                xe.SetValue(value.ToString().ReplaceLowOrderASCIICharacters());
            }
            return xe;
        }
        #endregion

        #region Execute

        public static void InitUpdateLog(XElement xe, string tableName =null)
        {
            if (!Twi.DataAccess.DBUtility.SqlExtension.IsWriteLog)
            {
                return;
            }
                //获得用户请求信息
                var request = OperationContext.Current.RequestContext.RequestMessage;

                if (request.State == MessageState.Closed)
                {
                    return;
                    // return "Http请求已关闭";
                }

                //// 获得Http请求
                //var requestProp = request.ToString();
                //XElement requestData = XElement.Parse(requestProp);
              
                if (string.IsNullOrEmpty(tableName))
                {
                    tableName = xe.Name.LocalName;
                }
            
              
                SqlExtension.SetRequestProperty("OperateType", 1);//1修改，2删除

                if (SqlExtension.GetRequestProperty("LogDataList") == null)
                {
                    SqlExtension.SetRequestProperty("LogDataList", new List<XElement>());
                }
              
                var logDataList = SqlExtension.GetRequestProperty("LogDataList") as List<XElement>;
                if (xe != null)
                {
                    logDataList.Add(xe);
                }
               
        }
        public static int ExecuteUpdate(this SqlCommand me, XElement xe, string tableName = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = xe.Name.LocalName;
            }

         


            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = GetMetaDataTable(me, tableName).Rows.OfType<DataRow>();
            var primaryKeys = GetMetaDataTablePrimaryKeys(me, tableName);//主键集合 

            foreach (XElement x in xe.Elements().Where(y => !new string[] { "CreateUserID", "CreateDate", "LastUpdateUserID", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName //匹配列名
                    && y["计算列"].ToString() == "0"  //非计算列                    
                    ).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数
                    me.SetParameter(x.ToSqlParameter(dr["类型"].ToString()));

                    if (!primaryKeys.Contains(x.Name.LocalName) //排除主检(包括组合键)
                        && ((dr["标识"].ToString() == "1" && dr["主键"].ToString() == "1") || (dr["标识"].ToString() == "0" && dr["主键"].ToString() == "0"))//标识主键列或非标识非主键列
                        )
                    {
                        setColumnList.Add(x);
                    }
                }
            }
           

            string cols = setColumnList.Select(x => x.Name.LocalName).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            me.CommandText = string.Format("select {0} from {1} where {2}", cols, tableName, primaryKeys.Select(x => string.Format("{0} = @{0}", x)).Aggregate((x, y) => string.Format("{0} AND {1}", x, y)));
            var old1 = me.GetSingle(tableName);
            var diff = CompareTo(xe,old1, null, primaryKeys);
            

            InitUpdateLog(diff, tableName);

            string setClause = setColumnList.Select(x => string.Format("{0} = @{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString().Contains("LastUpdateUserID")) != null) //表中是否包含最后更新人字段
            {
                setClause += ", LastUpdateUserID = @CurrentUserID, LastUpdateDate = GETDATE()";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString().Contains("LastUpdateUserName")) != null) //不是所有表都有
            {
                setClause += ", LastUpdateUserName = @CurrentUserName";
            }
            string primaryKeysValue = primaryKeys.Select(x => string.Format("{0} = @{0}", x)).Aggregate((x, y) => string.Format("{0} AND {1}", x, y));

          
            me.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, setClause
                , primaryKeysValue);

            foreach (SqlParameter param in me.Parameters)
            {
                primaryKeysValue = primaryKeysValue.Replace(param.ParameterName, param.Value.ToString());
            }
            if (SqlExtension.GetRequestProperty("PrimaryKeysValue") == null)
            {
                SqlExtension.SetRequestProperty("PrimaryKeysValue", primaryKeysValue);
            }
            return me.ExecuteNonQueryByLog();
        }


        /// <summary>
        /// 拿一个业务对象与相应的对象比对，获取服务器端能识别的比对格式
        /// </summary>
        /// <param name="me"></param>
        /// <param name="xeCounterpart">副本</param>
        /// <returns></returns>
        public static XElement CompareTo(XElement me, XElement xeCounterpart, XElement xeCounterpartDesc, params string[] reserverdElementNames)
        {
            if (me == null)
                return null;


            XElement xeDiff = new XElement(me.Name, new XAttribute("flag", "update"));
            foreach (XElement x in me.Elements().Where(x => !x.Name.LocalName.EndsWith("List") && (!x.HasElements || x.Element("NewValue") != null) && new string[] { "CreateDate", "LastUpdateDate", "Flag" }.Contains(x.Name.LocalName) == false))  //只限于当前表的字段
            {
                if (xeCounterpart.Element(x.Name) != null)
                {
                    if (x.Name == "BizRemark")
                    {
                        var s = "";
                    }
                    if( x.Value != xeCounterpart.Element(x.Name).Value)
                    {
                        var desc = "";
                        //if (xeCounterpartDesc != null)
                        //{
                        //   var a= xeCounterpartDesc.Elements();
                        //   if (a != null)
                        //   {
                        //       desc = a.GetValue();
                        //   }

                        //}
                        if(x.Element("NewValue") ==null)
                        {
                        xeDiff.Add(new XElement(x.Name, new XElement("Desc", desc), new XElement("NewValue", x.Value), new XElement("OldValue", xeCounterpart.Element(x.Name).Value)));
                        }
                        else
                        {
                            xeDiff.Add(new XElement(x.Name, new XElement("Desc", desc), new XElement("NewValue", x.Element("NewValue").Value), new XElement("OldValue", xeCounterpart.Element(x.Name).Value)));
                   
                        }
                    }
                }
            }
            if (xeDiff.HasElements)
            {
                foreach (string x in reserverdElementNames)
                {
                    if (xeDiff.Element(x) == null) //如果保留的字段已经是更新字段，就不必添加了
                    {
                        xeDiff.Add(xeCounterpart.Element(x));
                    }
                }
                return xeDiff;
            }
            else
            {
                return null;
            }
        }
 
        //返回新增记录的ID
        public static object ExecuteInsert(this SqlCommand me, XElement xe, string tableName = null)
        {
            if (string.IsNullOrEmpty(tableName))
            {
                tableName = xe.Name.LocalName;
            }
            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = GetMetaDataTable(me, tableName).Rows.OfType<DataRow>();
            foreach (XElement x in xe.Elements()
                .Where(y => !new string[] { "CreateUserID", "CreateDate", "LastUpdateUserID", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName //匹配列名
                   && y["计算列"].ToString() == "0"  //非计算列
                   && y["标识"].ToString() == "0" //非标识列
                   ).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数
                    me.SetParameter(x.ToSqlParameter(dr["类型"].ToString()));
                    setColumnList.Add(x);
                }
            }

            string insertIntoClause_Columns = setColumnList.Select(x => x.Name.LocalName).Aggregate(
                (x, y) => string.Format("{0}, {1}", x, y));
            string selectClause = setColumnList.Select(x => string.Format("{0} = @{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));


            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserID") != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserID";
                selectClause += ", CreateUserID = @CurrentUserID";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserName") != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserName";
                selectClause += ", CreateUserName = @CurrentUserName";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "LastUpdateDate") != null)
            {
                insertIntoClause_Columns += ", LastUpdateDate";
                selectClause += ", LastUpdateDate = GETDATE()";
            }

            me.CommandText = string.Format("INSERT INTO {0} ({1})  SELECT {2}", tableName, insertIntoClause_Columns, selectClause);

            //是否存在标识字段
            if (metaRows.FirstOrDefault(x => x["标识"].ToString() == "1") == null)
            {
                return me.ExecuteNonQuery();
            }
            else
            {
                //SqlParameter paraId = new SqlParameter("Id", SqlDbType.Int);
                //paraId.Direction = ParameterDirection.Output;
                //me.SetParameter(paraId);
                //me.CommandText += " SELECT @Id = SCOPE_IDENTITY()" ;
                //me.ExecuteNonQuery();
                //return paraId.Value;

                me.ExecuteNonQuery();

                //IDENT_CURRENT is not limited by scope it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope.
                //SCOPE_IDENTITY, @@IDENTITY 都不行，如果是在事物中，除了第一个ID能获取外，其他的都不能获取 

                me.CommandText = string.Format("SELECT IDENT_CURRENT('{0}')", tableName);
                return me.ExecuteScalar();

            }
        }

        #endregion

        public static SqlParameter ToSqlParameter(this XElement me, string sqlDbType)
        {
            //特殊字段的处理
            if (me.Name.LocalName == "SortOrder" && me.Value.ToString().Trim() == "")
            {
                SqlParameter p = new SqlParameter("@SortOrder", SqlDbType.Int);
                p.Value = 0;
                return p;
            }

            switch (sqlDbType)
            {
                case "uniqueidentifier":
                    return me.ToSqlParameter<Guid>();
                case "nvarchar":
                    return me.ToSqlParameter<string>();
                case "ntext":
                    return me.ToSqlParameter<string>();
                case "text":
                    return me.ToSqlParameter<string>();
                case "varchar":
                    goto case "nvarchar";
                case "int":
                    return me.ToSqlParameter<int>();
                case "bigint":
                    return me.ToSqlParameter<long>();
                case "datetime":
                    return me.ToSqlParameter<DateTime>();
                case "decimal":
                    return me.ToSqlParameter<decimal>();
                case "float":
                    return me.ToSqlParameter<float>();
                case "double":
                    return me.ToSqlParameter<double>();
                case "money":
                    return me.ToSqlParameter<decimal>();
                case "xml":
                    return me.ToSqlParameter<XElement>();

            }
            return null;
        }

        private static DataSet _metaDataSet = new DataSet();

        public static DataTable GetMetaDataTable(string tableName)
        {
            if (!_metaDataSet.Tables.Contains(tableName))
            {
                using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString))
                {
                    conn.Open();
                    SqlCommand cmd = conn.CreateCommand();
                    return cmd.GetMetaDataTable(tableName);
                }
            }
            return _metaDataSet.Tables[tableName];
        }

        //获取并缓存数据库表列的元数据信息
        public static DataTable GetMetaDataTable(this SqlCommand cmd, string tableName)
        {
            if (!_metaDataSet.Tables.Contains(tableName))
            {
                cmd.CommandText = string.Format(@"SELECT  
  表名 = case when a.colorder=1 then d.name else '' end,
  字段序号 = a.colorder,
  字段名 = a.name,
  标识 = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1'else '0' end,
  主键 = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (
  SELECT name FROM sysindexes WHERE indid in(
  SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end,
  类型 = b.name,
  占用字节数 = a.length,
  长度 = COLUMNPROPERTY(a.id,a.name,'PRECISION'),
  小数位数 = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
  允许空 = case when a.isnullable=1 then '1'else '0' end,
  默认值 = isnull(e.text,''),
  计算列 = iscomputed
FROM  
  syscolumns a
left join  
  systypes b  
on  
  a.xusertype=b.xusertype
inner join  
  sysobjects d  
on  
  a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join  
  syscomments e  
on  
  a.cdefault=e.id
where  
  d.name='{0}' --如果只查询指定表,加上此条件
order by  
  a.id,a.colorder", tableName);
                SqlDataAdapter sa = new SqlDataAdapter(cmd);
                sa.Fill(_metaDataSet, tableName);
            }
            return _metaDataSet.Tables[tableName];
        }

        //获取指定表的列名集合
        private static IEnumerable<string> GetMetaColumnNames(this SqlCommand cmd, string tableName)
        {
            return GetMetaDataTable(cmd, tableName).Rows.OfType<DataRow>().Select(x => x["字段名"].ToString());
        }

        //获取指定表的列名集合
        public static IEnumerable<string> GetMetaColumnNames(string tableName)
        {
            return GetMetaDataTable(tableName).Rows.OfType<DataRow>().Select(x => x["字段名"].ToString());
        }

        //获取元数据—主键
        public static string[] GetMetaDataTablePrimaryKeys(this SqlCommand me, string tableName)
        {
            return GetMetaDataTable(me, tableName).Rows.OfType<DataRow>().Where(x => x["主键"].ToString() == "1").Select(x => x["字段名"].ToString()).ToArray();
        }

        //获取元数据—主键
        public static string[] GetMetaDataTablePrimaryKeys(string tableName)
        {
            return GetMetaDataTable(tableName).Rows.OfType<DataRow>().Where(x => x["主键"].ToString() == "1").Select(x => x["字段名"].ToString()).ToArray();
        }

        /// <summary>
        /// 根据XElement生成SqlCommand
        /// </summary>
        /// <param name="xe"></param>
        /// <returns></returns>
        public static SqlCommand CreateInsertSqlCommand(this XElement me, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.SetParameters(parameters);

            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = GetMetaDataTable(me.Name.LocalName).Rows.OfType<DataRow>();
            foreach (XElement x in me.Elements()
                .Where(y => !new string[] { "CreateUserID", "CreateDate", "LastUpdateUserID", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName //匹配列名
                   && y["计算列"].ToString() == "0"  //非计算列
                   && y["标识"].ToString() == "0" //非标识列
                   ).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数
                    var p = x.ToSqlParameter(dr["类型"].ToString());
                    cmd.SetParameter(p);
                    setColumnList.Add(x);
                }
            }

            string insertIntoClause_Columns = setColumnList.Select(x => x.Name.LocalName).Aggregate(
                (x, y) => string.Format("{0}, {1}", x, y));
            string selectClause = setColumnList.Select(x => string.Format("{0} = @{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));

            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserID") != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserID";
                selectClause += ", CreateUserID = @CurrentUserID";
            }
            if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "CreateUserName") != null) //表中是否包含添加人
            {
                insertIntoClause_Columns += ", CreateUserName";
                selectClause += ", CreateUserName = @CurrentUserName";
            }

            cmd.CommandText = string.Format("INSERT INTO {0} ({1}) SELECT {2}", me.Name.LocalName, insertIntoClause_Columns, selectClause);


            return cmd;
        }

        public static SqlCommand CreateUpdateSqlCommand(this XElement me, SqlParameter[] parameters)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.SetParameters(parameters);

            //上传的节点名称跟数据库列名称相符合的节点集合            
            List<XElement> setColumnList = new List<XElement>();
            var metaRows = GetMetaDataTable(me.Name.LocalName).Rows.OfType<DataRow>();
            var primaryKeys = GetMetaDataTablePrimaryKeys(me.Name.LocalName);//主键集合 

            foreach (XElement x in me.Elements().Where(y => !new string[] { "CreateUserID", "CreateDate", "LastUpdateUserID", "LastUpdateDate" }.Contains(y.Name.LocalName)))
            {
                DataRow dr = metaRows.Where(y => y["字段名"].ToString() == x.Name.LocalName //匹配列名
                    && y["计算列"].ToString() == "0"  //非计算列                    
                    ).FirstOrDefault();
                if (dr != null)
                {
                    //添加参数
                    cmd.SetParameter(x.ToSqlParameter(dr["类型"].ToString()));

                    if (!primaryKeys.Contains(x.Name.LocalName) //排除主检(包括组合键)
                        && ((dr["标识"].ToString() == "1" && dr["主键"].ToString() == "1") || (dr["标识"].ToString() == "0" && dr["主键"].ToString() == "0"))//标识主键列或非标识非主键列
                        )
                    {
                        setColumnList.Add(x);
                    }
                }
            }

            string setClause = "";
            if (setColumnList.Count > 0)
            {
                setClause = setColumnList.Select(x => string.Format("{0} = @{0}", x.Name.LocalName)).Aggregate((x, y) => string.Format("{0}, {1}", x, y));
                if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "LastUpdateUserID") != null) //表中是否包含最后更新人字段
                {
                    setClause += ", LastUpdateUserID = @CurrentUserID, LastUpdateDate = GETDATE()";
                }
                if (metaRows.FirstOrDefault(x => x["字段名"].ToString() == "LastUpdateUserName") != null) //不是所有表都有
                {
                    setClause += ", LastUpdateUserName = @CurrentUserName";
                }
                cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", me.Name.LocalName, setClause
                , primaryKeys.Select(x => string.Format("{0} = @{0}", x)).Aggregate((x, y) => string.Format("{0} AND {1}", x, y)));
                return cmd;
            }
            else
            {
                return null;
            }
        }

        public static DataTable Pivot(this SqlCommand me, string keyColumn, string pivotNameColumn, string pivotValueColumn)
        {
            DataTable tmp = new DataTable();
            using (var dataValues = me.ExecuteReader())
            {
                DataRow r;
                string LastKey = "//dummy//";
                int i, pValIndex, pNameIndex;
                string s;
                bool FirstRow = true;
                // Add non-pivot columns to the data table:
                pValIndex = dataValues.GetOrdinal(pivotValueColumn);
                pNameIndex = dataValues.GetOrdinal(pivotNameColumn);
                for (i = 0; i <= dataValues.FieldCount - 1; i++)
                    if (i != pValIndex && i != pNameIndex)
                        tmp.Columns.Add(dataValues.GetName(i), dataValues.GetFieldType(i));
                r = tmp.NewRow();
                // now, fill up the table with the data:
                while (dataValues.Read())
                {
                    // see if we need to start a new row
                    if (dataValues[keyColumn].ToString() != LastKey)
                    {
                        // if this isn't the very first row, we need to add the last one to the table
                        if (!FirstRow)
                            tmp.Rows.Add(r);
                        r = tmp.NewRow();
                        FirstRow = false;
                        // Add all non-pivot column values to the new row:
                        for (i = 0; i <= dataValues.FieldCount - 3; i++)
                            r[i] = dataValues[tmp.Columns[i].ColumnName];
                        LastKey = dataValues[keyColumn].ToString();
                    }
                    // assign the pivot values to the proper column; add new columns if needed:
                    s = dataValues[pNameIndex].ToString();

                    if (!tmp.Columns.Contains(s))
                    {
                        DataColumn c = tmp.Columns.Add(s, dataValues.GetFieldType(pValIndex));
                        // set the index so that it is sorted properly:
                        int newOrdinal = c.Ordinal;
                        for (i = newOrdinal - 1; i >= dataValues.FieldCount - 2; i--)
                            if (c.ColumnName.CompareTo(tmp.Columns[i].ColumnName) < 0)
                                newOrdinal = i;
                        c.SetOrdinal(newOrdinal);
                    }
                    r[s] = dataValues[pValIndex];
                }
                // add that final row to the datatable:
                tmp.Rows.Add(r);
                // Close the DataReader

                dataValues.Close();
            }
            // and that's it!
            return tmp;
        }

        public static DataTable Pivot(this DataTable me)
        {
            DataTable tmp = new DataTable();
            //原表的第一列作为名称列, 该列的值不能出现重复值
            DataColumn fstCol = tmp.Columns.Add(me.Columns[0].ColumnName, me.Columns[0].DataType);
            fstCol.Caption = me.Columns[0].Caption;
            foreach (DataRow row in me.Rows)
            {
                tmp.Columns.Add(row[0].ToString());
            }

            for (int i = 1; i < me.Columns.Count; i++)
            {
                DataRow row = tmp.NewRow();
                tmp.Rows.Add(row);
                row[0] = me.Columns[i].Caption;
                for (int j = 0; j < me.Rows.Count; j++)
                {
                    row[j + 1] = me.Rows[j][i].ToString();
                }
            }
            return tmp;
        }
    
        public static int ExecuteNonQueryByLog(this SqlCommand cmd)
        {
            if (!Twi.DataAccess.DBUtility.SqlExtension.IsWriteLog)
            {
                return cmd.ExecuteNonQuery();
            }
            if (OperationContext.Current == null || OperationContext.Current.RequestContext == null)
            {
                return -1;
                //  return "服务端自请求";  //服务端自己调用自己
            }

            //获得用户请求信息
            var request = OperationContext.Current.RequestContext.RequestMessage;

            if (request.State == MessageState.Closed)
            {
                return -1;
                // return "Http请求已关闭";
            }

            if (SqlExtension.GetRequestProperty("Sql") == null)
            {
                SqlExtension.SetRequestProperty("Sql", new List<string>());
            }
          
            //保存下SQL语句，在最后执行
            string commandText = cmd.CommandText;

            var sqlList = SqlExtension.GetRequestProperty("Sql") as List<string>;
            StringBuilder declareBuilder = new StringBuilder();

            //SQL语句，之后记录到数据库中，后面增加一个空格的目的是为了配合下面的使用正则表达式替换
            string sql = cmd.CommandText + " ";

            //循环参数将SQL语句中的参数通过正则表达式替换成实际的参数值
            //还有一个更好的实现方式，是将sql语句中的参数找出，然后挨个替换
            foreach (SqlParameter param in cmd.Parameters)
            {
                string parameterName = param.ParameterName.StartsWith("@") ? param.ParameterName : "@" + param.ParameterName;
                string value = param.Value.ToString();

                switch (param.SqlDbType)
                {
                    case SqlDbType.BigInt:
                    case SqlDbType.Bit:
                    case SqlDbType.Decimal:
                    case SqlDbType.Float:
                    case SqlDbType.Int:
                    case SqlDbType.Money:
                    case SqlDbType.Real:
                    case SqlDbType.SmallInt:
                        {
                            break;
                        }
                    default:
                        {
                            value = string.Format("'{0}'", value);
                            break;
                        }

                }
                //将SQL语句中的参数通过正则表达式替换成实际的参数值
                sql = Regex.Replace(sql, string.Format(@"(?<g1>{0})(?=[,|\W*])", parameterName), value.ToString(), RegexOptions.IgnoreCase | RegexOptions.ExplicitCapture);


            }
          
            //将单个SQL语句加入到数组中
            sqlList.Add(sql);

            //约定：如果调用的方法中含有DELETE字样(不区分大小写)则认为是删除操作
            if (SqlExtension.GetRequestProperty("HttpOperationName").ToString().ToUpper().Contains("DELETE"))
            {
                SqlExtension.SetRequestProperty("OperateType", 2);//OperateType：1修改，2删除
                
            
            }
            cmd.CommandText = commandText;
            return cmd.ExecuteNonQuery();

        }

        private static bool _isWriteLog = false;

        public static bool IsWriteLog
        {
            get { return _isWriteLog; }
            set { _isWriteLog = value; }
        }

        #region 设置请求属性
        /// <summary>
        /// 设置请求属性
        /// </summary>
        /// <param name="name">属性名称</param>
        /// <param name="property">属性值</param>
        public static void SetRequestProperty(string name, object property)
        {
            //获得用户请求信息
            var request = OperationContext.Current.RequestContext.RequestMessage;

            if (request.State == MessageState.Closed)
            {
                return;
                // return "Http请求已关闭";
            }

            var poperties = request.Properties.Where(x => x.Key == name);
            if ((poperties == null || poperties.Count() < 1))
            {
                if (property != null)
                {
                    request.Properties.Add(name, property);
                }
            }
            else
            {
                request.Properties[name] = property;
            }
        }
        #endregion

        #region 获取请求属性值
        /// <summary>
        /// 获取请求属性值
        /// </summary>
        /// <param name="name">属性名称</param>
        /// <returns>属性值</returns>
        public static object GetRequestProperty(string name, bool getFromQueryWhenNull = false)
        {
            //获得用户请求信息
            var request = OperationContext.Current.RequestContext.RequestMessage;

            if (request.State == MessageState.Closed)
            {
                return null;
                // return "Http请求已关闭";
            }
            var poperties = request.Properties.Where(x => x.Key == name);
            if (poperties != null && poperties.Count() > 0)
            {
                return request.Properties[name];
            }
            return null;
        }
        #endregion
    }
}
